using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using DataAccess_Layer;

public static class Database
{
    //public static String ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename='D:\Users\IVen_Chan\Documents\Visual Studio 2010\Projects\SuperStar\BIN\bobo.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True";
    public static String ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BoBoConnectionString"].ToString();
    private static void solveASPDataDirectory()
    {
        if (AppDomain.CurrentDomain.GetData("DataDirectory") != null)
        {
            string dataDirPath = (string)AppDomain.CurrentDomain.GetData("DataDirectory");
            dataDirPath = dataDirPath.Substring(0, dataDirPath.IndexOf("WEB")) + "BIN";
            AppDomain.CurrentDomain.SetData("DataDirectory", dataDirPath);
        }
    }
	public static SqlConnection GetConnection()
	{
     //   solveASPDataDirectory();
        return new SqlConnection(ConnectionString);
	}

    public static DataTable Fill(DataTable table, String sql, params Object[] parameters)
    {
        SqlCommand command = Database.CreateCommand(sql, parameters);
        new SqlDataAdapter(command).Fill(table);
        command.Connection.Close();

        return table;
    }

    public static DataTable GetData(String sql, params Object[] parameters)
    {
        return Database.Fill(new DataTable(), sql, parameters);
    }

    public static int ExecuteNonQuery(String sql, params Object[] parameters)
    {
        try
        {
            SqlCommand command = Database.CreateCommand(sql, parameters);

            command.Connection.Open();
            int rows = command.ExecuteNonQuery();
            command.Connection.Close();

            return rows;
        }
        catch (System.Exception ex)
        {
            return 0;
        }

    }

    public static object ExecuteScalar(String sql, params Object[] parameters)
    {
        try
        {
            SqlCommand command = Database.CreateCommand(sql, parameters);

            command.Connection.Open();
            object value = command.ExecuteScalar();
            command.Connection.Close();

            return value;
        }
        catch (System.Exception ex)
        {
            return null;	
        }
 
    }

    private static SqlCommand CreateCommand(String sql, params Object[] parameters)
    {
        SqlCommand command = new SqlCommand(sql, Database.GetConnection());
        for (int i = 0; i < parameters.Length; i+=2)
        {
            command.Parameters.AddWithValue(parameters[i].ToString(), parameters[i + 1]);
        }
        return command;
    }
}
